/* CreateExcelFileByDB.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2008-2-20	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.imp;

import java.io.DataInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import system.base.ClientDownupfileInfo;
import system.base.ServerDownupfileInfo;
import framework.core.*;
import framework.core.DateUtil;

public class CreateExcelFileByDB implements GWBaseProcesser {
    
    public static String createExcelFilePath(DBConnection con,String serverPath,String tableName,String warehouseCode,String type,String lastUpdateTime)
    {
        String strFilePath;
        try{
            HSSFWorkbook tWb = createExcelFile(con,tableName,warehouseCode,type,lastUpdateTime);
            
            //保存文件
            String strTime = "";
            try{
                strTime = system.imp.DateUtil.getCurrTime();
            }
            catch(Exception e)
            {
            }
            String fileName;
            String filePath = serverPath + ConstDefine.EXCELFILE_SAVECREATEFILE_PATH;
            fileName ="download_" + strTime + "_" + tableName + ".xls";
            System.out.println("=========================CreateExcelFilePath:" + filePath+fileName);
            FileOutputStream fileOut = new FileOutputStream(filePath+fileName);
            tWb.write(fileOut);
            fileOut.close();
            
            //下载路径
            strFilePath = ConstDefine.EXCELFILE_DOWNLOADCREATEFILE_PATH + fileName;
            System.out.println("=========================DownLoadFilePath:" + strFilePath);
            
            //在服务器上传下载表中记录操作信息
            ServerDownupfileInfo sdfi = ServerDownupfileInfo.fetch();
            sdfi.setCreateTime(new framework.core.DateUtil().getFullString());
            sdfi.setCreateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
            sdfi.setCreateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
            sdfi.setDownloadtyp(ServerDownupfileInfo.DOWNLOADTYPE_DOWN);
            sdfi.setLastUpdateTime(new framework.core.DateUtil().getFullString() );
            sdfi.setLastUpdateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
            sdfi.setLastUpdateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
            sdfi.setParmsstr(strFilePath);
            sdfi.setDatatableName(tableName);
            sdfi.setParmstype(ServerDownupfileInfo.UPLOAD_PARMSTYPE_TEXT);
            sdfi.setStatus(ConstDefine.STR_YES);
            sdfi.setUpdownState(ServerDownupfileInfo.UPLOAD_STATE_SUCCESS);
            sdfi.setWarehouseUincode(warehouseCode);
            sdfi.save(con);         
        }
        catch(Exception e)
        {
            e.printStackTrace();
            
            strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
            try {
                //在服务器上传下载表中记录操作信息
                ServerDownupfileInfo sdfi = ServerDownupfileInfo.fetch();
                sdfi.setCreateTime(new framework.core.DateUtil().getFullString());
                sdfi.setCreateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
                sdfi.setCreateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
                sdfi.setDownloadtyp(ServerDownupfileInfo.DOWNLOADTYPE_DOWN);
                sdfi.setLastUpdateTime(new framework.core.DateUtil().getFullString() );
                sdfi.setLastUpdateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
                sdfi.setLastUpdateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
                sdfi.setParmsstr(strFilePath);
                sdfi.setDatatableName(tableName);
                sdfi.setParmstype(ServerDownupfileInfo.UPLOAD_PARMSTYPE_TEXT);
                sdfi.setStatus(ConstDefine.STR_YES);
                sdfi.setUpdownState(ServerDownupfileInfo.UPLOAD_STATE_SUCCESS);
                sdfi.setWarehouseUincode(warehouseCode);
                sdfi.save(con);  
            } catch (Exception e1) {
                //记录到日志文件
                e.printStackTrace();
            }  
        }
        return strFilePath;
    }
    
    //创建Excel文件
    private static HSSFWorkbook createExcelFile(DBConnection con,String tableName,String warehouseCode,String type,String lastUpdateTime) throws Exception{
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(tableName);
        
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        HSSFCellStyle cellStyle= workbook.createCellStyle();
        cellStyle.setFont(font);        
        
        ResultSet rs = null;
        DBPreparedStatement pst = null;
        
        int ColumnCount=0;
        int j=0;        
        int i=0;
        try{
            //设置查询参数
            Vector parms = new Vector();
            Vector vals = new Vector();
            if (!type.equalsIgnoreCase(ConstDefine.EXCELFILE_LOAD_TYPE_FULL))
            {
                parms.add("last_update_time > ?");
                vals.add(DateUtil.formatToLongDateString(lastUpdateTime, new DateUtil().getFullString()));
                if (!tableName.equalsIgnoreCase("T_PROVIDER") && !tableName.equalsIgnoreCase("T_WAREHOUSE") && !tableName.equalsIgnoreCase("T_CUSTOMER_STAFF"))
                {
                    if (tableName.equalsIgnoreCase("T_PRODUCT"))
                    {
                        parms.add("(WAREHOUSE_CODE = ? or warehouse_code='000000')");
                        vals.add(warehouseCode);
                    }
                    else
                    {
                        parms.add("WAREHOUSE_CODE = ?");
                        vals.add(warehouseCode);
                    }
                }
            }
            
            //得到查询结果
            rs=getData(con,tableName,parms,vals, pst, rs);
            
            // 在索引0的位置创建行
            HSSFRow rowHead = sheet.createRow((short)0);
            for (ColumnCount=1;ColumnCount<rs.getMetaData().getColumnCount()+1;ColumnCount++){
                //在索引0的位置创建单元格
                HSSFCell cell = rowHead.createCell((short)(ColumnCount-1));
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                // 定义单元格为字符串类型
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 在单元格中输入一些内容
                cell.setCellValue(rs.getMetaData().getColumnName(ColumnCount));
                cell.setCellStyle(cellStyle);                                               
            }
            while (rs.next()){
//                if (i==0){
//                    i+=1;
//                    for (j=1;j<ColumnCount;j++){
//                        // 在索引0的位置创建行
//                        HSSFRow row = sheet.createRow((short)i);
//                        HSSFCell cell = row.createCell((short)(j-1));   
//                        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//                        // 定义单元格为字符串类型
//                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//                        // 在单元格中输入一些内容
//                        cell.setCellValue(rs.getString(j)== null ? "" : rs.getString(j) ); 
//                        cell.setCellStyle(cellStyle);                                       
//                    }                   
//                }else{
                    i+=1;
                    HSSFRow row = sheet.createRow((short)i);
                    for (j=1;j<ColumnCount;j++){
                        
                        //在索引0的位置创建单元格
                        HSSFCell cell = row.createCell((short)(j-1));
                        cell.setEncoding(HSSFCell.ENCODING_UTF_16); 
                        // 定义单元格为字符串类型
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        // 在单元格中输入一些内容
                        cell.setCellValue(rs.getString(j)==null ? "" : rs.getString(j));
                        cell.setCellStyle(cellStyle);                                           
                    }    
                    //System.out.println(tableName +" row:" + i);
                //}
            }
            return workbook;
        }catch(Exception e){
            e.printStackTrace();
            throw new BizException("不能生成: <"+tableName+"> Excel文件，请稍后重试！");
        }
        finally{
            try{
                if (rs != null) rs.close();
                if(pst != null) pst.close();
            }    
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
    } 
    
    //得到要生成Excel的数据
    private static ResultSet getData(DBConnection con,String tableName,Vector parms,Vector vals, DBPreparedStatement pst, ResultSet rs) throws Exception {
       
        String sql = "";        

        
        sql = "select * from " + tableName;
        if (parms.size()>0)
        {
            sql += " where 0=0";
            for(int i=0;i<parms.size();i++)
            {
                sql += " and " + (String)parms.get(i);                
            }
            
        }
        
        pst = con.prepareStatement(sql);
        if (parms.size()>0)
        {
            for(int i=0;i<parms.size();i++)
            { 
                pst.setString(i+1, StringUtil.replaceSql((String)vals.get(i)));
            }
        }
        rs = pst.executeQuery();
        
        return rs;
    }
}
